package com.tool.cn.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具 类
 * 
 * @author wangzh 2016年9月18日 上午9:12:59
 */
@SuppressWarnings("all")
public final class ExcelUtil {

	private static final Log log = LogFactory.getLog(ExcelUtil.class);

	/**
	 * 生成excel复杂
	 */
	public static void createExcel(String name) {

		try {
			// 路径根据实际需要修改
			FileOutputStream output = new FileOutputStream(name);
			HSSFWorkbook wkb = new HSSFWorkbook();
			HSSFSheet sheet = null;

			// 分几个分页
			for (int i = 0; i < 3; i++) {

				sheet = wkb.createSheet("优惠券列表" + (i + 1));
				sheet.setColumnWidth(0, 6000);
				sheet.setColumnWidth(1, 6000);
				sheet.setColumnWidth(2, 6000);
				sheet.setColumnWidth(3, 6000);
				sheet.setColumnWidth(4, 6000);

				HSSFCellStyle style = wkb.createCellStyle();
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
				style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
				// 设置背景色
				style.setFillForegroundColor((short) 13);
				style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

				HSSFRow row2 = sheet.createRow(0);
				HSSFCell cell = row2.createCell(0);
				// 首行设置
				cell.setCellValue("编号");
				cell.setCellStyle(style);
				cell = row2.createCell(1);
				cell.setCellValue("优惠券编码");
				cell.setCellStyle(style);
				cell = row2.createCell(2);
				cell.setCellValue("状态");
				cell.setCellStyle(style);
				cell = row2.createCell(3);
				cell.setCellValue("会员");
				cell.setCellStyle(style);
				cell = row2.createCell(4);
				cell.setCellValue("更新时间");
				cell.setCellStyle(style);

				// 实体内容
				for (int j = 1; j < 201; j++) {
					HSSFRow row3 = sheet.createRow(j);
					row3.createCell(0).setCellValue("李明" + j);
					row3.createCell(1).setCellValue("As178");
					row3.createCell(2).setCellValue(j * j);
					row3.createCell(3).setCellValue(j + j);
				}

			}
			wkb.write(output);
			output.flush();
			output.close();
		} catch (FileNotFoundException e) {
			log.error("未发现文件" + e);
		} catch (IOException e) {
			log.error("创建excel读取异常" + e);
		}

	}

	/**
	 * 读取excel
	 */
	public static void readExcel(String xlsPath) {
		try {
			FileInputStream fileIn = new FileInputStream(xlsPath);
			Workbook wb=null;
			 if(checkVersion(xlsPath)){
                wb=new XSSFWorkbook(fileIn);
            }else{
                wb=new HSSFWorkbook(fileIn);
            }

			Sheet sht0 = wb.getSheetAt(0); // 获取第一个表单
			for (Row r : sht0) {
				if (r.getRowNum() < 1) {
					continue;
				}
				// 创建实体类
				if (r.getCell(0) != null) {
					r.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING); // Int类型转换String
					System.out.println(r.getCell(0).getStringCellValue().trim()); // 第一列
				}
				if (r.getCell(1) != null) {
					r.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
					System.out.println(r.getCell(0).getStringCellValue().trim()); // 第二列
				}
				if (r.getCell(2) != null) {
					r.getCell(2).setCellType(HSSFCell.CELL_TYPE_STRING);
					System.out.println(r.getCell(0).getStringCellValue().trim()); // 第三列
				}
			}
			fileIn.close();
		} catch (FileNotFoundException e) {
			log.error("文件不存在或文件不可操作" + e);
		} catch (IOException e) {
			log.error("读写IO流异常" + e);
		} catch (Exception e) {
			log.error(e);
		}
	}
	
	 /**
     * 是否为2007版本
     * @param url
     * @return
     */
    public static boolean checkVersion(String url){
        return url.matches("^.+\\.(?i)(xlsx)$");  
    }
    
	/**
	 * 读取excel
	 * @param sheet 表单好从0开始
	 * @param column 列
	 */
	public static String readExcel(String xlsPath,int sheet,int column) {
		try {
			FileInputStream fileIn = new FileInputStream(xlsPath);
			Workbook wb=null;
			 if(checkVersion(xlsPath)){
                wb=new XSSFWorkbook(fileIn);
            }else{
                wb=new HSSFWorkbook(fileIn);
            }

			Sheet sht0 = wb.getSheetAt(sheet); // 获取表单 从0开始
			StringBuffer buf=new StringBuffer();
			String context;
			for (Row r : sht0) {
				if (r.getRowNum() < 1) {
					continue;
				}
				if (r.getCell(1) != null) {
					r.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
					context=r.getCell(1).getStringCellValue();
					if(StringUtil.isNotEmpty(context)){
						buf.append(context).append(",");
					}else{
						break;
					}
					
				}
				if (r.getCell(2) != null) {
					r.getCell(2).setCellType(HSSFCell.CELL_TYPE_STRING);
					context=r.getCell(2).getStringCellValue();
					if(StringUtil.isNotEmpty(context)){
						buf.append(context).append("\n");
					}else{
						break;
					}
					
				}
			}
			fileIn.close();
			return buf.toString();
		} catch (FileNotFoundException e) {
			log.error("文件不存在或文件不可操作" + e);
		} catch (IOException e) {
			log.error("读写IO流异常" + e);
		} catch (Exception e) {
			log.error(e);
		}
		return "";
	}
}
